package org.darkforest.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {

    /**
     * 读取Excel数据内容
     *
     * @return Map 包含单元格数据内容的Map对象
     * @throws Exception
     */
    public static Map<Integer, Map<Integer, String>> readExcelContent(File excelFile) throws Exception {

        Map<Integer, Map<Integer, String>> content = new HashMap<Integer, Map<Integer, String>>();
        Map<Integer, String> rowContent = null;

        try {

            InputStream inputStream = new FileInputStream(excelFile);

            Row row = null;
            if(excelFile.getName().endsWith("xls")) {     //Excel 2003
                HSSFWorkbook wb = new HSSFWorkbook(inputStream);
                // sheet 对应一个工作页
                HSSFSheet sheet = wb.getSheetAt(0);

                int startRowNum = sheet.getFirstRowNum();
                int endRowNum = sheet.getLastRowNum();
                for (int rowNum = startRowNum; rowNum <= endRowNum; rowNum++) {
                    row = sheet.getRow(rowNum);
                    if ((row == null) || (row.getCell(0) == null)) {
                        break;
                    }
                    rowContent = rowOneContent(row);
                    content.put(rowNum, rowContent);
                }
            } else if(excelFile.getName().endsWith("xlsx")){    // Excel 2007/2010
                XSSFWorkbook xwb = new XSSFWorkbook(inputStream);
                // sheet 对应一个工作页
                XSSFSheet sheet = xwb.getSheetAt(0);

                int startRowNum = sheet.getFirstRowNum();
                int endRowNum = sheet.getLastRowNum();
                for (int rowNum = startRowNum; rowNum <= endRowNum; rowNum++) {
                    row = sheet.getRow(rowNum);
                    if ((row == null) || (row.getCell(0) == null)) {
                        break;
                    }

                    rowContent = rowOneContent(row);
                    content.put(rowNum, rowContent);
                }
            }

            return content;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            throw e;
        } catch (IOException e) {
            e.printStackTrace();
            throw e;
        }
    }

    private static Map<Integer, String> rowOneContent(Row row) throws Exception {
        Map<Integer, String> rowContent = new HashMap<Integer, String>();

        int startCellNum = row.getFirstCellNum();
        int endCellNum = row.getLastCellNum();
        for (int cellNum = startCellNum; cellNum < endCellNum; cellNum++) {
            Cell cell = row.getCell(cellNum);
            if (cell == null) {
                rowContent.put(cellNum, "");
                continue;
            }

            String strCell = null;
            int type = cell.getCellType();
            switch (type) {
                case Cell.CELL_TYPE_NUMERIC:// 数值、日期类型
                    double d = cell.getNumericCellValue();
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {// 日期类型
                        // Date date = cell.getDateCellValue();
                        Date date = HSSFDateUtil.getJavaDate(d);
                        strCell = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
                    } else {// 数值类型
                        strCell = String.valueOf(new java.text.DecimalFormat("0").format(d));
                    }
                    break;
                case Cell.CELL_TYPE_BLANK:// 空白单元格
                    strCell = " ";
                    break;
                case Cell.CELL_TYPE_STRING:// 字符类型
                    strCell = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_BOOLEAN:// 布尔类型
                    strCell = String.valueOf(cell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_ERROR: // 故障
                    strCell = "非法字符"; // 非法字符;
                    break;
                default:
                    strCell = "error"; // 未知类型
                    break;
            }

            rowContent.put(cellNum, strCell);
        }

        return rowContent;
    }
}
